import MySQLdb
import MySQLdb.cursors
from legocalendar.models.reswell.AbstractWrapper import AbstractWrapper


class MySQLwrapper(AbstractWrapper):
    """A wrapper that connects to a MySQL database."""

    schema = {}
    
    def __init__(self, config, key):
        super(MySQLwrapper, self).__init__(config)
        self.key = key
        self.setSchema()

    def getConnection(self):
        """ Returns a MySQLdb connection object based on this wrapper's configuration """
        return MySQLdb.connect(
            host   = self.config['host'],
            port   = self.config['port'],
            user   = self.config['username'],
            passwd = self.config['password'],
            db     = self.config['name'])
            #cursorclass=MySQLdb.cursors.DictCursor)

    def setSchema(self):
        """ Queries the remote DB and builds a local schema. """
        self.schema["name"] = self.config["name"]
        self.schema["tables"] = []

        con = None
        try:
            con = self.getConnection()
            cur = con.cursor()
            cur.execute("show tables")
            tablenames = [s for row in cur.fetchall() for s in row]

            cur = con.cursor(MySQLdb.cursors.DictCursor)
            for tname in tablenames:
                cur.execute("desc " + tname)
                rows = cur.fetchall() # row = dictionary
                
                # skip to the next table if this table doesn't contain the join key
                if self.key not in [row['Field'] for row in rows]:
                    print("MySQL Wrapper Warning: excluded table '"+tname+"' because it doesn't contain field '"+self.key+"'")
                    continue

                table = {"tablename" : tname,
                         "key" : self.key, #filter(lambda r: r["Key"]=="PRI", rows)[0]["Field"],
                         "fields" : []}

                for r in rows:
                    table["fields"].append({"fieldname" : r["Field"],
                                            "alternate" : r["Field"]})
                
                self.schema["tables"].append(table)
        finally:
            if con: con.close()


    def getSchema(self):
        """ Returns a dictionary representing this data source's schema """
        return self.schema


    def query(self, db):
        """
        Takes a dictionary in Reswell Query format, constructs a SQL query, and queries the
        database. Returns a tuple of all rows returned by the query.
        """
        con = self.getConnection()
        c = con.cursor()
        tables = []
        filters = []
        fields = []
        g = lambda x: x['left']+x['op']+x['right'] #convert filter into string
        for table in db['tables']:
            tables.append(table['name'])
            for filt in table['filters']:
                filters.append(g(filt))
            for field in table['fields']:
                fields.append(field)
        query = "select %s from %s where %s" %(", ".join(fields), joinTables(tables), " and ".join(filters))
        c.execute(query)
        allRows = c.fetchall()
        data = dict()
        for row in allRows:
            data[row[0]] = row[1:] #index each row by its myid
        return(data)

    def joinTables(self, tables):
        q = tables[0]
        for table in tables[1:]:
            q += " join "+table+" using("+self.key+")"
        return q
        
